﻿using System.Data;
using System.Drawing;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Dynamic;
using System.Reflection;
using System.Text;
using ApiLib.Common.Attributes;
using SixLabors.ImageSharp.PixelFormats;
using SqlSugar;

namespace ApiLib.Utils;

/// <summary>
/// Excel工具：需要NPOI，NPOI.Mapper
/// </summary>
public static class ExcelUtil
{
    /*/// <summary>
    /// List转Excel<br/>
    /// 实体类属性加上注解：[Column("号码")],[Column("时间",DefaultValue = "",CustomFormat = "yyyy-MM-dd HH:mm:ss")]<br/>
    /// 返回文件：FileResult=>File(stream.ToArray(), "application/vnd.ms-excel","列表.xlsx");<br/>
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list">数据</param>
    /// <param name="sheetName">表名</param>
    /// <param name="overwrite">true,覆盖单元格，false追加内容(list和创建的excel或excel模板)</param>
    /// <param name="xlsx">true-xlsx，false-xls</param>
    /// <returns>返回流</returns>
    public static MemoryStream ParseListToExcel<T>(List<T> list, string sheetName = "sheet1", bool overwrite = true,
        bool xlsx = true) where T : class
    {
        var mapper = new Mapper();
        var ms = new MemoryStream();
        mapper.Save(ms, list, sheetName, overwrite, xlsx);
        return ms;
    }

    /// <summary>
    /// Excel转为List<br/>
    /// IFormFile=>var formFile = Request.Form.Files[0];<br/>
    /// using var stream = formFile.OpenReadStream();<br/>
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="fileStream">流</param>
    /// <param name="sheetName">表名</param>
    /// <returns>返回列表数据</returns>
    public static List<T> ParseExcelToList<T>(Stream fileStream, string sheetName = "") where T : class
    {
        var mapper = new Mapper(fileStream);
        var modelList = new List<T>();
        var dataList = !string.IsNullOrEmpty(sheetName)
            ? mapper.Take<T>(sheetName).ToList()
            : mapper.Take<T>().ToList();
        if (dataList is { Count: > 0 })
        {
            modelList.AddRange(dataList.Select(item => item.Value));
        }

        return modelList;
    }*/

    /// <summary>
    /// 根据Excel文件类型返回IWorkbook
    /// </summary>
    /// <param name="fileName">文件路径</param>
    /// <returns></returns>
    public static IWorkbook GetWorkbook(string fileName)
    {
        if (File.Exists(fileName))
        {
            using var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (Path.GetExtension(fileName).Equals(".xlsx")) return new XSSFWorkbook(fs);
            return new HSSFWorkbook(fs);
        }

        if (Path.GetExtension(fileName).Equals(".xlsx")) return new XSSFWorkbook();
        return new HSSFWorkbook();
    }

    /// <summary>
    /// 使用Excel特性
    /// </summary>
    /// <returns></returns>
    public static bool IsExcel<T>()
    {
        return typeof(T).GetCustomAttribute(typeof(ExcelAttribute), false) is ExcelAttribute ||
               typeof(T).GetCustomAttribute(typeof(DataAttribute), false) is DataAttribute;
    }

    #region 读取Excel

    /// <summary>
    /// 数据转Excel文件
    /// </summary>
    /// <param name="workbook">表单</param>
    /// <returns></returns>
    private static List<T> GenerateExcelData<T>(IWorkbook workbook)
    {
        var result = new List<T>();
        var type = typeof(T);
        GenerateExcelData(workbook, type, () => type.GetInstance<T>(), o => result.Add((T)o));
        return result;
    }

    /// <summary>
    /// 数据转Excel文件
    /// </summary>
    /// <param name="workbook">表单</param>
    /// <param name="type"></param>
    /// <returns></returns>
    private static List<object> GenerateExcelData(IWorkbook workbook, Type type)
    {
        var result = new List<object>();
        GenerateExcelData(workbook, type, type.GetInstance, o => result.Add(o));
        return result;
    }

    private static void GenerateExcelData(IWorkbook workbook, Type type, Func<object?> buildObjFunc,
        Action<object> buildResult)
    {
        var sheetAttr = type.GetExcelAttribute();
        /*var sheetAttr = type.GetCustomAttribute(typeof(ExcelAttribute), true) as ExcelAttribute;
        sheetAttr ??= new ExcelAttribute(type.Name);*/
        var sheet = workbook.GetSheet(sheetAttr.Name) ?? workbook.GetSheetAt(0);
        if (sheet == null) return;

        var firstRow = sheet.GetRow(0);
        if (firstRow == null) return;

        var cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

        var propertyInfos = type.GetProperties();
        var propNames = new Dictionary<string, PropertyInfo>();
        var propCols = new Dictionary<int, PropertyInfo>();
        var propAttrs = new Dictionary<PropertyInfo, ExcelAttribute>();
        foreach (var propertyInfo in propertyInfos)
        {
            var headerAttr = propertyInfo.GetExcelAttribute();
            /*var headerAttr = propertyInfo.GetCustomAttribute(typeof(ExcelAttribute), true) as ExcelAttribute;
            headerAttr ??= new ExcelAttribute(propertyInfo.Name);*/
            if (headerAttr.IsIgnore) continue;
            propAttrs[propertyInfo] = headerAttr;
            propNames[headerAttr.Name] = propertyInfo;
        }

        for (var i = 0; i < cellCount; ++i)
        {
            var cell = firstRow.GetCell(i);
            var cellValue = cell?.StringCellValue;
            if (string.IsNullOrEmpty(cellValue)) continue;
            if (propNames.TryGetValue(cellValue, out var propertyInfo))
            {
                propCols[i] = propertyInfo;
            }
        }

        var startRow = sheet.FirstRowNum + 1;

        var rowCount = sheet.LastRowNum; //最后一列的标号
        for (var i = startRow; i <= rowCount; ++i)
        {
            var row = sheet.GetRow(i);
            if (row == null) continue; //没有数据的行默认是null　　　　　　　

            var instance = buildObjFunc.Invoke();
            if (instance == null)
            {
                break;
            }

            for (var j = 0; j < cellCount; ++j)
            {
                if (propCols.TryGetValue(j, out var propertyInfo) &&
                    propAttrs.TryGetValue(propertyInfo, out var excelAttribute))
                {
                    var cell = row.GetCell(j);
                    var cellData = excelAttribute.IsImage ? cell.GetImage() : cell.GetCellValue();
                    var data = excelAttribute.ParseData(cellData, propertyInfo);

                    /*var value = cellValue.ToString();
                    if (propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?))
                    {
                        var format = excelAttribute.DateFormat;
                        if (!string.IsNullOrEmpty(value) && value.TryToDateTime(format, out var dt))
                        {
                            cellValue = dt;
                        }
                    }
                    else if (propertyInfo.PropertyType == typeof(bool) || propertyInfo.PropertyType == typeof(bool?))
                    {
                        if (value is "是" or "1")
                        {
                            cellValue = true;
                        }
                        else
                        {
                            cellValue = bool.TryParse(value, out var boolVal) && boolVal;
                        }
                    }*/

                    propertyInfo.SetFieldValue(instance, data);
                }
            }

            buildResult.Invoke(instance);
        }
    }

    #endregion

    #region 生成Excel

    /// <summary>
    /// 数据转Excel文件
    /// </summary>
    /// <param name="enumerables"></param>
    /// <returns></returns>
    private static IWorkbook GenerateExcelWorkbook(params IEnumerable[] enumerables)
    {
        var workbook = new XSSFWorkbook();
        foreach (var enumerable in enumerables)
        {
            var type = enumerable.GetType().GenericTypeArguments[0];
            if (enumerable is IList { Count: > 0 } listTmp)
            {
                var list0 = listTmp[0];
                if (list0 != null)
                {
                    type = list0.GetType();
                }
            }

            var sheetAttr = type.GetExcelAttribute();

            var sheet = workbook.CreateSheet(sheetAttr.Name);
            if (sheetAttr.Width > 0)
            {
                sheet.DefaultRowHeight = (short)(sheetAttr.Width * 256);
            }

            if (sheetAttr.Height > 0)
            {
                sheet.DefaultRowHeight = (short)(sheetAttr.Height * 20);
            }

            var colIndex = 0;
            var rowIndex = 0;
            var row = sheet.CreateRow(rowIndex++);
            var propertyInfos = type.GetProperties();
            var excelAttributes = new Dictionary<PropertyInfo, ExcelAttribute>();
            var excelStyles = new Dictionary<PropertyInfo, ICellStyle>();
            foreach (var propertyInfo in propertyInfos)
            {
                var headerAttr = propertyInfo.GetExcelAttribute();
                /*var headerAttr = propertyInfo.GetCustomAttribute(typeof(ExcelAttribute), true) as ExcelAttribute;
                headerAttr ??= new ExcelAttribute(propertyInfo.Name);*/
                excelAttributes[propertyInfo] = headerAttr;
                if (excelAttributes[propertyInfo].IsIgnore) continue;

                var cell = row.CreateCell(colIndex++);
                cell.SetCellValue(headerAttr.Name);
                {
                    var headerStyle = GetCellStyle(sheet, headerAttr, true);
                    cell.CellStyle = headerStyle;
                }
                {
                    var cellStyle = GetCellStyle(sheet, headerAttr, false);
                    excelStyles[propertyInfo] = cellStyle;
                    if (headerAttr.Width > 0)
                    {
                        sheet.SetColumnWidth(colIndex - 1, headerAttr.Width * 256);
                    }
                }
            }

            foreach (var obj in enumerable)
            {
                colIndex = 0;
                row = sheet.CreateRow(rowIndex++);
                foreach (var propertyInfo in propertyInfos)
                {
                    var excelAttribute = excelAttributes[propertyInfo];
                    if (excelAttribute.IsIgnore) continue;

                    var fieldValue = propertyInfo.GetFieldValue(obj);
                    var cell = row.CreateCell(colIndex++);
                    cell.CellStyle = excelStyles[propertyInfo];

                    var value = excelAttribute.GetData(fieldValue, propertyInfo);
                    if (excelAttribute.IsImage && value is List<byte[]> bytes)
                    {
                        foreach (var bs in bytes)
                        {
                            sheet.SaveImage(bs, cell.RowIndex, cell.ColumnIndex);
                        }
                    }
                    else
                    {
                        cell.SetCellValue(value);
                    }
                    /*if (excelAttribute.IsFile)
                    {
                        if (fieldValue is string filePath)
                        {
                            using var fileStream = File.OpenRead(filePath);
                            using var stream = new MemoryStream();
                            fileStream.CopyTo(stream);
                            sheet.SaveImage(stream.ToArray(),cell.RowIndex,cell.ColumnIndex);
                        }
                        else if (fieldValue is FileInfo fileInfo)
                        {
                            using var fileStream = fileInfo.OpenRead();
                            using var stream = new MemoryStream();
                            fileStream.CopyTo(stream);
                            sheet.SaveImage(stream.ToArray(),cell.RowIndex,cell.ColumnIndex);
                        }
                    }
                    //时间
                    else if (propertyInfo.PropertyType == typeof(DateTime) ||
                        propertyInfo.PropertyType == typeof(DateTime?))
                    {
                        if (fieldValue is DateTime dt && dt != DateTime.MinValue)
                        {
                            cell.SetCellValue(dt.ToString(excelAttribute.DateFormat));
                        }
                        else
                        {
                            cell.SetCellValue("");
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(excelAttribute.ReadConverterExp))
                        {
                            if (fieldValue is string strValue && !string.IsNullOrEmpty(strValue))
                            {
                                var split = excelAttribute.ReadConverterExp.Split(",")
                                    .FirstOrDefault(expStr => expStr.Contains(strValue));
                                if (!string.IsNullOrEmpty(split))
                                {
                                    fieldValue = split[(split.IndexOf('=') + 1)..];
                                }
                            }
                        }

                        //默认字符串
                        if (fieldValue == null && !string.IsNullOrEmpty(excelAttribute.DefaultValue))
                        {
                            fieldValue = excelAttribute.DefaultValue;
                        }

                        //前缀
                        if (!string.IsNullOrEmpty(excelAttribute.Prefix))
                        {
                            fieldValue = excelAttribute.Prefix + fieldValue;
                        }

                        //后缀
                        if (!string.IsNullOrEmpty(excelAttribute.Suffix))
                        {
                            fieldValue += excelAttribute.Suffix;
                        }

                        cell.SetCellValue(fieldValue);
                    }*/
                }
            }
        }

        return workbook;
    }

    /// <summary>
    /// 获取样式
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="excel">特性</param>
    /// <param name="header">是否Header</param>
    /// <returns></returns>
    private static ICellStyle GetCellStyle(ISheet sheet, ExcelAttribute excel, bool header = true)
    {
        var font = (XSSFFont)sheet.Workbook.CreateFont();
        font.SetColor(GetXssfColor(header ? excel.HeaderFontColor : excel.FontColor));
        font.FontHeightInPoints = header ? excel.HeaderFontSize : excel.FontSize;
        font.FontName = header ? excel.HeaderFontName : excel.FontName;
        font.IsBold = header ? excel.HeaderFontBold : excel.FontBold;
        font.IsItalic = header ? excel.HeaderFontItalic : excel.FontItalic;

        var style = (XSSFCellStyle)sheet.Workbook.CreateCellStyle();
        style.SetFillForegroundColor(GetXssfColor(header ? excel.HeaderBackgroundColor : excel.BackgroundColor));
        style.FillPattern = FillPattern.SolidForeground;
        style.SetFont(font);
        style.WrapText = excel.WrapText;
        style.Alignment = (HorizontalAlignment)excel.Alignment;
        style.VerticalAlignment = (VerticalAlignment)excel.VerticalAlignment;
        style.BorderBottom = BorderStyle.Thin;
        style.BorderLeft = BorderStyle.Thin;
        style.BorderRight = BorderStyle.Thin;
        style.BorderTop = BorderStyle.Thin;
        return style;
    }

    /// <summary>
    /// 获取颜色
    /// </summary>
    /// <param name="color"></param>
    /// <returns></returns>
    private static XSSFColor GetXssfColor(Color color)
    {
        // return new XSSFColor([color.R, color.G, color.B]);
        return new XSSFColor(new SixLabors.ImageSharp.Color(new Argb32(color.R, color.G, color.B, color.A)));
    }

    #endregion

    #region Excel特性

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="list2"></param>
    /// <param name="list3"></param>
    /// <param name="list4"></param>
    /// <param name="list5"></param>
    public static IWorkbook GetExcelWorkbook<T, T2, T3, T4, T5>(this IEnumerable<T> list, IEnumerable<T2> list2,
        IEnumerable<T3> list3, IEnumerable<T4> list4, IEnumerable<T5> list5)
    {
        return GenerateExcelWorkbook(list, list2, list3, list4, list5);
    }

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="list2"></param>
    /// <param name="list3"></param>
    /// <param name="list4"></param>
    public static IWorkbook GetExcelWorkbook<T, T2, T3, T4>(this IEnumerable<T> list, IEnumerable<T2> list2,
        IEnumerable<T3> list3, IEnumerable<T4> list4)
    {
        return GenerateExcelWorkbook(list, list2, list3, list4);
    }

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="list2"></param>
    /// <param name="list3"></param>
    public static IWorkbook GetExcelWorkbook<T, T2, T3>(this IEnumerable<T> list, IEnumerable<T2> list2,
        IEnumerable<T3> list3)
    {
        return GenerateExcelWorkbook(list, list2, list3);
    }

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="list2"></param>
    public static IWorkbook GetExcelWorkbook<T, T2>(this IEnumerable<T> list, IEnumerable<T2> list2)
    {
        return GenerateExcelWorkbook(list, list2);
    }

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    public static IWorkbook GetExcelWorkbook<T>(this IEnumerable<T> list) where T : new()
    {
        return GenerateExcelWorkbook(list);
    }

    /// <summary>
    /// 导出Workbook，根据Excel特性
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="type"></param>
    public static IWorkbook GetExcelWorkbook(this IEnumerable<ExpandoObject> list, Type type)
    {
        var objects = list.SugarParseToObj(type);
        return GenerateExcelWorkbook(objects);
    }

    /// <summary>
    /// 数据转Excel文件
    /// </summary>
    /// <param name="enumerables"></param>
    /// <returns></returns>
    public static IWorkbook GetExcelWorkbook(params IEnumerable[] enumerables)
    {
        try
        {
            return GenerateExcelWorkbook(enumerables);
        }
        catch (Exception e)
        {
            WebUtil.LogExp(e.Message);
            throw;
        }
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="t2List"></param>
    /// <param name="t3List"></param>
    /// <param name="t4List"></param>
    /// <param name="t5List"></param>
    /// <typeparam name="T"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <typeparam name="T3"></typeparam>
    /// <typeparam name="T4"></typeparam>
    /// <typeparam name="T5"></typeparam>
    /// <returns></returns>
    public static List<T> GetExcelData<T, T2, T3, T4, T5>(this IWorkbook workbook, out List<T2> t2List,
        out List<T3> t3List, out List<T4> t4List, out List<T5> t5List)
    {
        var list = GenerateExcelData<T>(workbook);
        t2List = GenerateExcelData<T2>(workbook);
        t3List = GenerateExcelData<T3>(workbook);
        t4List = GenerateExcelData<T4>(workbook);
        t5List = GenerateExcelData<T5>(workbook);
        return list;
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="t2List"></param>
    /// <param name="t3List"></param>
    /// <param name="t4List"></param>
    /// <typeparam name="T"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <typeparam name="T3"></typeparam>
    /// <typeparam name="T4"></typeparam>
    /// <returns></returns>
    public static List<T> GetExcelData<T, T2, T3, T4>(this IWorkbook workbook, out List<T2> t2List, out List<T3> t3List,
        out List<T4> t4List)
    {
        var list = GenerateExcelData<T>(workbook);
        t2List = GenerateExcelData<T2>(workbook);
        t3List = GenerateExcelData<T3>(workbook);
        t4List = GenerateExcelData<T4>(workbook);
        return list;
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="t2List"></param>
    /// <param name="t3List"></param>
    /// <typeparam name="T"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <typeparam name="T3"></typeparam>
    /// <returns></returns>
    public static List<T> GetExcelData<T, T2, T3>(this IWorkbook workbook, out List<T2> t2List, out List<T3> t3List)
    {
        var list = GenerateExcelData<T>(workbook);
        t2List = GenerateExcelData<T2>(workbook);
        t3List = GenerateExcelData<T3>(workbook);
        return list;
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="t2List"></param>
    /// <typeparam name="T"></typeparam>
    /// <typeparam name="T2"></typeparam>
    /// <returns></returns>
    public static List<T> GetExcelData<T, T2>(this IWorkbook workbook, out List<T2> t2List)
    {
        var list = GenerateExcelData<T>(workbook);
        t2List = GenerateExcelData<T2>(workbook);
        return list;
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static List<T> GetExcelData<T>(this IWorkbook workbook)
    {
        return GenerateExcelData<T>(workbook);
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="types"></param>
    /// <returns></returns>
    public static List<object> GetExcelData(this IWorkbook workbook, params Type[] types)
    {
        var data = new List<object>();
        foreach (var type in types)
        {
            data.AddRange(GenerateExcelData(workbook, type));
        }

        return data;
    }

    #endregion
}

/// <summary>
/// 表格扩展
/// </summary>
public static class ExcelExtend
{
    #region Excel特性

    /// <summary>
    /// 获取Excel特性
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    public static ExcelAttribute GetExcelAttribute(this Type type)
    {
        var sheetAttr = type.GetCustomAttribute(typeof(ExcelAttribute), false) as ExcelAttribute;
        sheetAttr ??= new ExcelAttribute();
        if (string.IsNullOrEmpty(sheetAttr.Name))
        {
            if (type.GetCustomAttribute(typeof(SugarTable), false) is SugarTable sugarTable)
            {
                sheetAttr.Name = sugarTable.TableDescription;
            }
            else
            {
                sheetAttr.Name = type.Name;
            }
        }

        return sheetAttr;
    }

    /// <summary>
    /// 获取Excel特性
    /// </summary>
    /// <param name="propertyInfo"></param>
    /// <returns></returns>
    public static ExcelAttribute GetExcelAttribute(this PropertyInfo propertyInfo)
    {
        var headerAttr = propertyInfo.GetCustomAttribute(typeof(ExcelAttribute), false) as ExcelAttribute;
        headerAttr ??= new ExcelAttribute();
        if (string.IsNullOrEmpty(headerAttr.Name))
        {
            if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), false) is ColumnAttribute column)
            {
                headerAttr.Name = column.ColumnDescription;
                headerAttr.IsIgnore = column.IsIgnoreExcel;
                headerAttr.IsImage = column.IsExcelImage;
            }
            else if (propertyInfo.GetCustomAttribute(typeof(SugarColumn), false) is SugarColumn sugarColumn)
            {
                headerAttr.Name = sugarColumn.ColumnDescription;
            }
            else
            {
                headerAttr.Name = propertyInfo.Name;
            }
        }

        return headerAttr;
    }

    #endregion

    #region Workbook扩展

    /// <summary>
    /// 获取单元格的值
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    public static object GetCellValue(this ICell? cell)
    {
        /*return cell.CellType switch
        {
            CellType.Boolean => cell.BooleanCellValue,
            CellType.Numeric => cell.NumericCellValue,
            _ => cell.StringCellValue
        };*/
        if (cell == null)
        {
            return "";
        }

        switch (cell.CellType)
        {
            case CellType.String:
                return cell.StringCellValue;
            case CellType.Numeric:
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue;
                }

                return cell.NumericCellValue;
            case CellType.Boolean:
                return cell.BooleanCellValue;
            case CellType.Error:
                return cell.ErrorCellValue;
            case CellType.Formula:
            /*var sheet = cell.Sheet;
            BaseFormulaEvaluator evaluator = sheet is XSSFSheet
                ? new XSSFFormulaEvaluator(sheet.Workbook)
                : new HSSFFormulaEvaluator(sheet.Workbook);
            var formulaValue = evaluator.Evaluate(cell);
            return formulaValue.CellType switch
            {
                CellType.Numeric => formulaValue.NumberValue,
                CellType.String => formulaValue.StringValue,
                _ => cell.ToString()??""
            };*/

            default:
                return cell.ToString() ?? "";
        }
    }

    /// <summary>
    /// 设置值，自顶匹配类型
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="value"></param>
    public static void SetCellValue(this ICell cell, object? value)
    {
        var newVal = value?.ToString() ?? "";
        if (value is bool boolVal)
        {
            cell.SetCellValue(boolVal);
        }
        else if (value is DateTime dateTime)
        {
            cell.SetCellValue(dateTime);
        }
        else if (double.TryParse(newVal, out var doubleVal))
        {
            cell.SetCellType(CellType.Numeric);
            cell.SetCellValue(doubleVal);
        }
        else
        {
            cell.SetCellValue(newVal);
        }
    }

    /// <summary>
    /// Image存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="pictureData">图片数据</param>
    /// <param name="row">起始行：0开始</param>
    /// <param name="col">起始列：0开始</param>
    /// <param name="resize">是否使用原大小，为false时仅占用设置行列的大小，为true时使用图片原大小</param>
    public static void SaveImage(this ISheet sheet, byte[] pictureData, int row, int col, bool resize = false)
    {
        sheet.SaveImage(pictureData, row, col, row + 1, col + 1, resize);
    }

    /// <summary>
    /// Image存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="pictureData">图片数据</param>
    /// <param name="row">起始行：0开始</param>
    /// <param name="col">起始列：0开始</param>
    /// <param name="row2">结束行：不包括当前</param>
    /// <param name="col2">结束列：不包括当前</param>
    /// <param name="resize">是否使用原大小，为false时仅占用设置行列的大小，为true时使用图片原大小</param>
    public static void SaveImage(this ISheet sheet, byte[] pictureData, int row, int col, int row2, int col2,
        bool resize = false)
    {
        /*using var fileStream = File.OpenRead(filePath);
        using var stream = new MemoryStream();
        fileStream.CopyTo(stream);*/
        // var patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
        var patriarch = sheet.DrawingPatriarch;
        var pictureIdx = sheet.Workbook.AddPicture(pictureData, PictureType.PNG);
        var anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col2, row2);
        var pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
        if (resize)
        {
            pict.Resize();
        }
    }


    /// <summary>
    /// Image存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="stream">图片</param>
    /// <param name="row">起始行：0开始</param>
    /// <param name="col">起始列：0开始</param>
    /// <param name="resize">是否使用原大小，为false时仅占用设置行列的大小，为true时使用图片原大小</param>
    public static void SaveImage(this ISheet sheet, MemoryStream stream, int row, int col, bool resize = false)
    {
        sheet.SaveImage(stream.ToArray(), row, col, row + 1, col + 1, resize);
    }

    /// <summary>
    /// Image存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="stream">图片</param>
    /// <param name="row">起始行：0开始</param>
    /// <param name="col">起始列：0开始</param>
    /// <param name="row2">结束行：不包括当前</param>
    /// <param name="col2">结束列：不包括当前</param>
    /// <param name="resize">是否使用原大小，为false时仅占用设置行列的大小，为true时使用图片原大小</param>
    public static void SaveImage(this ISheet sheet, MemoryStream stream, int row, int col, int row2, int col2,
        bool resize = false)
    {
        sheet.SaveImage(stream.ToArray(), row, col, row2, col2, resize);
    }

    /// <summary>
    /// 读取图片
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    public static List<byte[]> GetImage(this ICell cell)
    {
        var datas = new List<byte[]>();
        if (cell.Sheet.DrawingPatriarch is XSSFDrawing drawing)
        {
            foreach (var shape in drawing.GetShapes())
            {
                if (shape is XSSFPicture picture)
                {
                    var row1 = picture.GetPreferredSize().Row1;
                    var col1 = picture.GetPreferredSize().Col1;
                    if (cell.RowIndex == row1 && cell.ColumnIndex == col1)
                    {
                        datas.Add(picture.PictureData.Data);
                    }
                }
            }

            return datas;
        }

        if (cell.Sheet.DrawingPatriarch is HSSFPatriarch patriarch)
        {
            foreach (var shape in patriarch.GetShapes())
            {
                if (shape is HSSFPicture picture)
                {
                    var row1 = picture.GetPreferredSize().Row1;
                    var col1 = picture.GetPreferredSize().Col1;
                    if (cell.RowIndex == row1 && cell.ColumnIndex == col1)
                    {
                        datas.Add(picture.PictureData.Data);
                    }
                }
            }
            return datas;
        }

        return default;
    }

    #endregion

    #region 表格转数据

    /// <summary>
    /// Sheet读取DataTable
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns></returns>
    public static List<Dictionary<string, object?>> ToDictionary(this ISheet sheet, bool isFirstRowColumn = true)
    {
        var dataTable = sheet.ToDataTable(isFirstRowColumn);
        return dataTable.ToListDictionary();
    }

    /// <summary>
    /// Sheet读取DataTable
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns></returns>
    public static DataTable ToDataTable(this ISheet sheet, bool isFirstRowColumn = true)
    {
        var data = new DataTable();
        var firstRow = sheet.GetRow(0);
        if (firstRow == null) return data;

        int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
        int startRow;

        #region 设置列名

        if (isFirstRowColumn)
        {
            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
            {
                var cell = firstRow.GetCell(i);
                var cellValue = cell?.StringCellValue;
                if (cellValue != null)
                {
                    var column = new DataColumn(cellValue);
                    data.Columns.Add(column);
                }
            }

            startRow = sheet.FirstRowNum + 1;
        }
        else
        {
            for (int i = firstRow.FirstCellNum; i < cellCount; i++)
            {
                var column = new DataColumn(i.ToString());
                data.Columns.Add(column);
            }

            startRow = sheet.FirstRowNum;
        }

        #endregion

        var rowCount = sheet.LastRowNum; //最后一列的标号
        for (var i = startRow; i <= rowCount; ++i)
        {
            var row = sheet.GetRow(i);
            if (row == null) continue; //没有数据的行默认是null　　　　　　　

            var dataRow = data.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; ++j)
            {
                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                    // dataRow[j] = row.GetCell(j).ToString();
                    dataRow[j] = row.GetCell(j).GetCellValue();
            }

            data.Rows.Add(dataRow);
        }

        return data;
    }

    /// <summary>
    /// 将excel中的数据导入到DataTable中
    /// </summary>
    /// <param name="workbook">表</param>
    /// <param name="sheetName">excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public static DataTable ToDataTable(this IWorkbook workbook, bool isFirstRowColumn = true, string? sheetName = null)
    {
        ISheet? sheet = null;
        if (sheetName != null)
        {
            sheet = workbook.GetSheet(sheetName);
        }

        sheet ??= workbook.GetSheetAt(0);
        return sheet.ToDataTable(isFirstRowColumn);
    }

    /// <summary>
    /// 将excel中的数据导入到DataSet中
    /// </summary>
    /// <param name="workbook">表</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
    /// <returns>返回的DataTable</returns>
    public static DataSet ToDataSet(this IWorkbook workbook, bool isFirstRowColumn = true)
    {
        var ds = new DataSet();
        for (var e = 0; e < workbook.NumberOfSheets; e++)
        {
            var sheet = workbook.GetSheetAt(e);
            if (sheet == null) continue;
            var data = sheet.ToDataTable(isFirstRowColumn);
            ds.Tables.Add(data);
        }

        return ds;
    }


    /// <summary>
    /// workbook表转对象集合
    /// </summary>
    /// <param name="workbook">表对象</param>
    /// <param name="dictionary">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static List<T> ToEntityList<T>(this IWorkbook workbook, Dictionary<string, string>? dictionary = null)
        where T : new()
    {
        var sheet = workbook.GetSheetAt(0);
        return sheet.ToEntityList<T>(dictionary);
    }

    /// <summary>
    /// workbook表转对象集合
    /// </summary>
    /// <param name="workbook">表对象</param>
    /// <param name="obj">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static List<T> ToEntityList<T>(this IWorkbook workbook, object? obj = null) where T : new()
    {
        var sheet = workbook.GetSheetAt(0);
        return sheet.ToEntityList<T>(obj);
    }

    /// <summary>
    /// sheet表转对象集合
    /// </summary>
    /// <param name="sheet">表对象</param>
    /// <param name="obj">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static List<T> ToEntityList<T>(this ISheet sheet, object? obj = null) where T : new()
    {
        Dictionary<string, string>? columns = null;
        if (obj != null)
        {
            var dict = obj.ToDictionary();
            columns = new Dictionary<string, string>();
            foreach (var (key, value) in dict)
            {
                columns.TryAdd(key, value as string ?? "");
            }
        }

        return ToEntityList<T>(sheet, columns);
    }

    /// <summary>
    /// sheet表转对象集合
    /// </summary>
    /// <param name="sheet">表对象</param>
    /// <param name="dictionary">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static List<T> ToEntityList<T>(this ISheet sheet, Dictionary<string, string>? dictionary = null)
        where T : new()
    {
        var entityList = new List<T>();
        if (dictionary == null || dictionary.Count == 0)
        {
            dictionary = new Dictionary<string, string>();
            var properties = typeof(T).GetProperties();
            properties.ForEach(info => dictionary.Add(info.Name, ""));
        }

        var firstRow = sheet.GetRow(0);
        var cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
        var columnNames = new List<string>();
        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
        {
            var cell = firstRow.GetCell(i);
            if (cell != null)
            {
                var cellValue = cell.StringCellValue;
                columnNames.Add(cellValue);
            }
            else
            {
                columnNames.Add("");
            }
        }

        var startRow = sheet.FirstRowNum + 1;
        var rowCount = sheet.LastRowNum; //最后一列的标号
        for (var i = startRow; i <= rowCount; ++i)
        {
            var row = sheet.GetRow(i);
            var info = new T();
            foreach (var (key, value) in dictionary)
            {
                var columnName = string.IsNullOrEmpty(value) ? key : value;
                if (string.IsNullOrEmpty(columnName))
                {
                    continue;
                }

                var indexOf = columnNames.IndexOf(columnName);
                if (indexOf >= 0)
                {
                    var cellValue = row.GetCell(indexOf).GetCellValue();
                    info.SetFieldValue(key, cellValue);
                }
            }

            entityList.Add(info);
        }

        return entityList;
    }

    #endregion

    #region 数据转表格

    /// <summary>
    /// DataTable存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="dt">DataTable</param>
    public static void Save(this ISheet sheet, DataTable dt)
    {
        var row = sheet.CreateRow(0); //在工作表中添加一行
        for (var i = 0; i < dt.Columns.Count; i++)
        {
            var cell = row.CreateCell(i); //在行中添加一列
            cell.SetCellValue(dt.Columns[i].ColumnName); //设置列的内容	 
        }

        for (var i = 1; i <= dt.Rows.Count; i++) //遍历DataTable行
        {
            var dataRow = dt.Rows[i - 1];
            row = sheet.CreateRow(i); //在工作表中添加一行

            for (var j = 0; j < dt.Columns.Count; j++) //遍历DataTable列
            {
                var cell = row.CreateCell(j); //在行中添加一列
                cell.SetCellValue(dataRow[j]); //设置列的内容	 
            }
        }
    }

    /// <summary>
    /// 将集合存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="list">集合对象</param>
    /// <param name="columns">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static void Save<T>(this ISheet sheet, IEnumerable<T> list, object? columns = null) where T : class, new()
    {
        columns ??= new T();
        var dictionary = columns.ToDictionary();
        sheet.Save(list, dictionary);
        /*var colIndex = 0;
        var rowIndex = 0;
        var row = sheet.CreateRow(rowIndex++); //在工作表中添加一行
        foreach (var (key, value) in dictionary)
        {
            var cell = row.CreateCell(colIndex++);
            var columnName = value == null || string.IsNullOrEmpty(value.ToString()) ? key : value.ToString();
            cell.SetCellValue(columnName);
        }

        foreach (var info in list)
        {
            colIndex = 0;
            row = sheet.CreateRow(rowIndex++);
            foreach (var key in dictionary.Keys)
            {
                var cell = row.CreateCell(colIndex++); //在行中添加一列
                var value = info.GetFieldValue(key);
                cell.SetCellValue(value);
            }
        }*/
    }

    /// <summary>
    /// 将集合存储到Sheet
    /// </summary>
    /// <param name="sheet">表</param>
    /// <param name="list">集合对象</param>
    /// <param name="dictionary">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static void Save<T>(this ISheet sheet, IEnumerable<T> list, Dictionary<string, object?> dictionary)
        where T : class, new()
    {
        var colIndex = 0;
        var rowIndex = 0;
        var row = sheet.CreateRow(rowIndex++); //在工作表中添加一行
        foreach (var (key, value) in dictionary)
        {
            var cell = row.CreateCell(colIndex++);
            var columnName = value == null || string.IsNullOrEmpty(value.ToString()) ? key : value.ToString();
            cell.SetCellValue(columnName);
        }

        foreach (var info in list)
        {
            colIndex = 0;
            row = sheet.CreateRow(rowIndex++);
            foreach (var key in dictionary.Keys)
            {
                var cell = row.CreateCell(colIndex++); //在行中添加一列
                var propertyInfo = typeof(T).GetProperty(key);
                var value = info.GetFieldValue(key);
                //时间
                if (propertyInfo != null && (propertyInfo.PropertyType == typeof(DateTime) ||
                                             propertyInfo.PropertyType == typeof(DateTime?)))
                {
                    if (value is DateTime dt && dt != DateTime.MinValue)
                    {
                        cell.SetCellValue(dt.ToCommonFormat());
                    }
                    else
                    {
                        cell.SetCellValue("");
                    }
                }
                else
                {
                    cell.SetCellValue(value);
                }
            }
        }
    }

    /// <summary>
    /// 将字典集合存储到Sheet
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="list">集合对象</param>
    public static void Save(this ISheet sheet, List<Dictionary<string, object>> list)
    {
        if (list.Count <= 0) return;
        var colIndex = 0;
        var rowIndex = 0;
        var row = sheet.CreateRow(rowIndex++); //在工作表中添加一行
        foreach (var key in list[0].Keys)
        {
            var cell = row.CreateCell(colIndex++);
            cell.SetCellValue(key);
        }

        foreach (var info in list)
        {
            colIndex = 0;
            row = sheet.CreateRow(rowIndex++);
            foreach (var value in info.Values)
            {
                var cell = row.CreateCell(colIndex++); //在行中添加一列
                cell.SetCellValue(value); //设置列的内容
            }
        }
    }


    /// <summary>
    /// 将DataTable中的数据导入到Workbook中
    /// </summary>
    /// <param name="dt">DataTable</param>
    public static IWorkbook ToWorkbook(this DataTable dt)
    {
        var workbook = new XSSFWorkbook();
        var sheet = string.IsNullOrEmpty(dt.TableName)
            ? workbook.CreateSheet("Sheet1")
            : workbook.CreateSheet(dt.TableName); //创建工作表
        sheet.Save(dt);
        return workbook;
    }

    /// <summary>
    /// 将DataSet中的数据导入到Workbook中
    /// </summary>
    /// <param name="ds">DataSet</param>
    public static IWorkbook ToWorkbook(this DataSet ds)
    {
        var workbook = new XSSFWorkbook();
        for (var d = 0; d < ds.Tables.Count; d++)
        {
            var dt = ds.Tables[d];
            var sheet = string.IsNullOrEmpty(dt.TableName)
                ? workbook.CreateSheet("Sheet" + d)
                : workbook.CreateSheet(dt.TableName); //创建工作表
            sheet.Save(dt);
        }

        return workbook;
    }


    /// <summary>
    /// 将集合数据导入到Workbook中
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="columns">导出列，属性名与Excel列名得到匹配，为空时默认导出全部</param>
    public static IWorkbook ToWorkbook<T>(this IEnumerable<T> list, object? columns = null) where T : class, new()
    {
        var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(typeof(T).Name);
        sheet.Save(list, columns);
        return workbook;
    }

    /// <summary>
    /// 将DataSet中的数据导入到Workbook中
    /// </summary>
    /// <param name="list">集合对象</param>
    /// <param name="dictionary">导出列，字段名=>列名</param>
    public static IWorkbook ToWorkbook<T>(this IEnumerable<T> list, Dictionary<string, object?> dictionary)
        where T : class, new()
    {
        var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(typeof(T).Name);
        sheet.Save(list, dictionary);
        return workbook;
    }

    /// <summary>
    /// 将DataSet中的数据导入到Workbook中
    /// </summary>
    /// <param name="list">集合对象</param>
    public static IWorkbook ToWorkbook(this List<Dictionary<string, object>> list)
    {
        var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet();
        sheet.Save(list);
        return workbook;
    }

    #endregion


    /// <summary>
    /// Workbook存储到流
    /// </summary>
    /// <param name="workbook"></param>
    public static MemoryStream Write(this IWorkbook workbook)
    {
        var ms = new MemoryStream();
        workbook.Write(ms);
        return ms;
    }

    /// <summary>
    /// Workbook存储到文件
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="path"></param>
    public static void Write(this IWorkbook workbook, string path)
    {
        using var ms = new MemoryStream();
        workbook.Write(ms);
        ms.Write(path);
    }

    /// <summary>
    /// 获取Workbook对象
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    public static IWorkbook GetWorkbook(this IFormFile file)
    {
        using var readStream = file.OpenReadStream();
        if (Path.GetExtension(file.FileName).Equals(".xlsx"))
        {
            return new XSSFWorkbook(readStream);
        }

        return new HSSFWorkbook(readStream);
    }

    /// <summary>
    /// 读取CSV数据
    /// </summary>
    /// <param name="file"></param>
    /// <param name="map">列名=>字段名</param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static List<T> GetCsvData<T>(this IFormFile file, Dictionary<string, string> map) where T : class, new()
    {
        using var readStream = file.OpenReadStream();
        using var reader = new StreamReader(readStream);
        var titles = new List<string>();
        var infos = new List<T>();
        while (reader.ReadLine() is { } line)
        {
            var cols = line.Trim().Split(",");
            if (titles.Count == 0)
            {
                cols.ForEach(col => titles.Add(col));
            }
            else
            {
                var info = new T();
                for (var i = 0; i < titles.Count; i++)
                {
                    if (map.TryGetValue(titles[i], out var fieldName))
                    {
                        info.SetFieldValue(fieldName, cols[i].Replace("\"", ""));
                    }
                }

                infos.Add(info);
            }
        }

        return infos;
    }

    /// <summary>
    /// 导出CSV文件
    /// </summary>
    /// <param name="list"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static MemoryStream ExportCsv<T>(IList<T> list)
    {
        var type = typeof(T);
        var properties = type.GetProperties();
        var props = new List<PropertyInfo>();
        var colValues = new List<string>();
        foreach (var property in properties)
        {
            var excel = property.GetCustomAttribute<ExcelAttribute>(false);
            excel ??= new ExcelAttribute(property.Name);
            if (!excel.IsIgnore)
            {
                props.Add(property);
                colValues.Add(excel.Name);
            }
        }

        var memoryStream = new MemoryStream();
        using var writer = new StreamWriter(memoryStream, Encoding.UTF8);
        writer.Write(string.Join(",", colValues));
        writer.WriteLine();
        foreach (var x1 in list)
        {
            var value = "";
            foreach (var prop in props)
            {
                if (!string.IsNullOrEmpty(value))
                {
                    value += ",";
                }

                value += $"\"{prop.GetValue(x1)?.ToString() ?? ""}\"";
            }

            writer.Write(value);
            writer.WriteLine();
        }

        return memoryStream;
    }
}